package org.lewis.common.jdbc;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.tocersoft.framework.core.cache.OSCacheManager;

/**
 * 数据库查询助手类
 * 
 * @author taiqichao
 * 
 */
public class QueryHelper {

	private static final Log log = LogFactory.getLog(QueryHelper.class);

	private final static QueryRunner queryRunner = new QueryRunner();

	private final static OSCacheManager cacheMang = OSCacheManager.getInstance();

	private final static ColumnListHandler columnListHandler = new ColumnListHandler() {
		protected Object handleRow(ResultSet rs) throws SQLException {
			Object obj = super.handleRow(rs);
			if (obj instanceof BigInteger)
				return ((BigInteger) obj).longValue();
			return obj;
		}
	};

	private final static ScalarHandler scalarHandler = new ScalarHandler() {
		public Object handle(ResultSet rs) throws SQLException {
			Object obj = super.handle(rs);
			if (obj instanceof BigInteger)
				return ((BigInteger) obj).longValue();
			return obj;
		}
	};

	private final static List<Class<?>> primitiveClasses = new ArrayList<Class<?>>() {
		private static final long serialVersionUID = 1L;
		{
			add(Long.class);
			add(Integer.class);
			add(String.class);
			add(java.util.Date.class);
			add(java.sql.Date.class);
			add(java.sql.Timestamp.class);
		}
	};

	private final static boolean isPrimitive(Class<?> cls) {
		return cls.isPrimitive() || primitiveClasses.contains(cls);
	}

	/**
	 * 获取数据库连接
	 * 
	 * @return
	 */
	public static Connection getConnection() {
		try {
			return DBManager.getConnection();
		} catch (SQLException e) {
			log.error("Get connection failed. " + e);
		}
		return null;
	}

	/**
	 * 返回单个对象
	 * 
	 * @param <T>
	 * @param cls
	 *            对象类型
	 * @param sql
	 *            SQL
	 * @param params
	 *            参数数组
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static <T> T get(Class<T> cls, String sql, Object... params) {
		try {
			return (T) queryRunner.query(getConnection(), sql,
					isPrimitive(cls) ? scalarHandler : new BeanHandler(cls),
					params);
		} catch (SQLException e) {
			log.error("Error get a object:" + e);
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	public static <T> T getCache(Class<T> cls, String cacheKey, String sql,
			Object... params) {
		T obj = (T) cacheMang.getCache(cacheKey);
		if (obj == null) {
			obj = get(cls, sql, params);
			cacheMang.putCache(cacheKey, obj);
		}
		return obj;
	}

	@SuppressWarnings("unchecked")
	public static <T> T getCache(Class<T> cls, String cacheKey,
			int refreshPeriod, String cronExpression, String sql,
			Object... params) {
		T obj = (T) cacheMang.getCache(cacheKey, refreshPeriod, cronExpression);
		if (obj == null) {
			obj = get(cls, sql, params);
			cacheMang.putCache(cacheKey, obj);
		}
		return obj;
	}

	@SuppressWarnings("unchecked")
	public static <T> T getCache(Class<T> cls, String cacheKey,
			int refreshPeriod, String sql, Object... params) {
		T obj = (T) cacheMang.getCache(cacheKey, refreshPeriod);
		if (obj == null) {
			obj = get(cls, sql, params);
			cacheMang.putCache(cacheKey, obj);
		}
		return obj;
	}

	/**
	 * 对象查询
	 * 
	 * @param <T>
	 * @param cls
	 *            对象类型
	 * @param sql
	 *            SQL
	 * @param params
	 *            参数数组
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static <T> List<T> query(Class<T> cls, String sql, Object... params) {
		try {
			return (List<T>) queryRunner.query(getConnection(), sql,
					isPrimitive(cls) ? columnListHandler : new BeanListHandler(
							cls), params);
		} catch (SQLException e) {
			log.error("Query object error:" + e);
		}
		return null;
	}

	/**
	 * 支持缓存抓取集合
	 * 
	 * @param <T>
	 * @param cls
	 *            对象类型
	 * @param cacheKey
	 *            缓存key
	 * @param sql
	 *            SQL
	 * @param params
	 *            参数数组
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static <T> List<T> queryCache(Class<T> cls, String cacheKey,
			int refreshPeriod, String cronExpression, String sql,
			Object... params) {
		List<T> objs = (List<T>) cacheMang.getCache(cacheKey, refreshPeriod,
				cronExpression);
		if (objs == null) {
			objs = query(cls, sql, params);
			cacheMang.putCache(cacheKey, objs);
		}
		return objs;
	}

	/**
	 * 支持缓存抓取集合
	 * 
	 * @param <T>
	 * @param cls
	 *            对象类型
	 * @param cacheKey
	 *            缓存key
	 * @param sql
	 *            SQL
	 * @param params
	 *            参数数组
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static <T> List<T> queryCache(Class<T> cls, String cacheKey,
			int refreshPeriod, String sql, Object... params) {
		List<T> objs = (List<T>) cacheMang.getCache(cacheKey, refreshPeriod);
		if (objs == null) {
			objs = query(cls, sql, params);
			cacheMang.putCache(cacheKey, objs);
		}
		return objs;
	}

	@SuppressWarnings("unchecked")
	public static <T> List<T> queryCache(Class<T> cls, String cacheKey,
			String sql, Object... params) {
		List<T> objs = (List<T>) cacheMang.getCache(cacheKey);
		if (objs == null) {
			objs = query(cls, sql, params);
			cacheMang.putCache(cacheKey, objs);
		}
		return objs;
	}

	/**
	 * 分页查询
	 * 
	 * @param <T>
	 * @param cls
	 *            对象类型
	 * @param sql
	 *            SQL
	 * @param page
	 *            页码
	 * @param count
	 *            页大小
	 * @param params
	 *            参数
	 * @return
	 */
	public static <T> List<T> queryPage(Class<T> cls, String sql, int page,
			int count, Object... params) {
		if (page < 0 || count < 0) {
			throw new IllegalArgumentException(
					"Illegal parameter of 'page' or 'count', Must be positive.");
		}
		int pageIndex = (page - 1) * count;
		count = (count > 0) ? count : Integer.MAX_VALUE;
		return query(cls, sql + " LIMIT ?,?", ArrayUtils.addAll(params,
				new Integer[] { pageIndex, count }));
	}

	/**
	 * 支持缓存的分页查询
	 * 
	 * @param <T>
	 * @param cls
	 *            对象类型
	 * @param cacheKey
	 *            缓存key
	 * @param cacheSize
	 *            缓存大小
	 * @param sql
	 *            SQL
	 * @param page
	 *            页码
	 * @param count
	 *            页大小
	 * @param params
	 *            参数
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static <T> List<T> queryPageCache(Class<T> cls, String cacheKey,
			int cacheSize, String sql, int page, int count, Object... params) {
		List<T> objs = (List<T>) cacheMang.getCache(cacheKey);
		if (objs == null) {
			objs = queryPage(cls, sql, 1, cacheSize, params);
			cacheMang.putCache(cacheKey, objs);
		}
		if (objs == null || objs.size() == 0)
			return objs;
		int from = (page - 1) * count;
		if (from < 0)
			return null;
		if ((from + count) > cacheSize)// 超出缓存的范围
			return queryPage(cls, sql, page, count, params);
		int end = Math.min(from + count, objs.size());
		if (from >= end)
			return null;
		return objs.subList(from, end);
	}

	/**
	 * 执行统计查询语句，语句的执行结果必须只返回一个数值
	 * 
	 * @param sql
	 *            SQL
	 * @param params
	 *            数组
	 * @return
	 */
	public static long count(String sql, Object... params) {
		try {
			Number num = (Number) queryRunner.query(getConnection(), sql,
					scalarHandler, params);
			return (num != null) ? num.longValue() : -1;
		} catch (SQLException e) {
			log.error("Get count error:" + e);
		}
		return 0;
	}

	/**
	 * 执行统计查询语句，语句的执行结果必须只返回一个数值
	 * 
	 * @param cacheKey
	 * @param sql
	 * @param params
	 * @return
	 */
	public static long statCache(String cacheKey, String sql, Object... params) {
		Number value = (Number) cacheMang.getCache(cacheKey);
		if (value == null) {
			value = count(sql, params);
			cacheMang.putCache(cacheKey, value);
		}
		return value.longValue();
	}

	/**
	 * 执行insert/update/delete语句
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public static int update(String sql, Object... params) {
		try {
			return queryRunner.update(getConnection(), sql, params);
		} catch (SQLException e) {
			log.error("update error:" + e);
		}
		return 0;
	}
	
	/**
	 * 批量执行指定的SQL语句
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public static int[] batch(String sql, Object[][] params) {
		try {
			return queryRunner.batch(getConnection(), sql, params);
		} catch (SQLException e) {
			log.error("batch error:" + e);
		}
		return null;
	}

}
